package com.cstam.dao;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.cstam.domain.News;

@Repository
public class NewsDao {
	//private static Logger LOG = Logger.getLogger(UserDaoImpl.class);
	//private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;

	@Autowired
	//@Qualifier("dataSource")
	public void setDataSource(DataSource dataSource) {
		//this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public int createNews(News news) {
		int ret = update("INSERT INTO cstam.news (author, time, title, text, type ) VALUES (?, now(), ?, ?, ?)",
				new Object[] {news.getAuthor(), news.getTime(), news.getTitle(), news.getText(), news.getType()});
		return ret;
	}
	
	public int modifyNews(News news) {
		int ret = update("UPDATE cstam.news SET author = ?, time = now(), title = ?, text = ?, type = ? WHERE idnews = ?",
				new Object[] {news.getAuthor(), news.getTime(), news.getTitle(), news.getText(), news.getType(), news.getNewsid()});
		return ret;
	}
	
	public int removeNews(int newsId) {
		int ret = update("DELETE FROM cstam.news where idnews = ?",
				new Object[] { newsId });
		return ret;
	}

	/*public List<News> getNewsByKey(String keywords) {
		List<News> news = query("SELECT * FROM cstam.news",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class));
		return news;
	}*/
	
	public News getNewsById(int idnews) {
		List<News> news = query("SELECT * FROM cstam.news WHERE idnews = ? ",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class),
				new Object[] { idnews });

		if (news.size() > 0)
			return news.get(0);
		else
			return null;
	}

	public List<News> getNewsByAuthor(String author) {
		List<News> news = query("SELECT * FROM cstam.news WHERE author like \'%?%\'",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class),
				new Object[] { author });

		if (news.size() != 0)
			return news;
		else
			return null;
	}
	
	public List<News> getNewsByTitle(String title) {
		//this must query one, otherwise throw exception
		/*User user = jdbcTemplate.queryForObject("SELECT * FROM cstam.user WHERE username = ? ", 
				ParameterizedBeanPropertyRowMapper.newInstance(User.class),
				new Object[] { username });*/
		List<News> news = query("SELECT * FROM cstam.news WHERE title like \'%?%\'",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class),
				new Object[] { title });

		if (news.size() != 0)
			return news;
		else
			return null;
	}
	
	public List<News> getNewsByTime(int num) {
		List<News> news = query("SELECT * FROM cstam.news order by time DESC limit 0,?",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class),
				new Object[] { num });

		if (news.size() != 0)
			return news;
		else
			return null;
	}
	
	//SELECT * FROM cstam.user ORDER BY iduser desc LIMIT 10,10;
	public List<News> getNewsByTime(int num_A, int num_B) {
		List<News> news = query("SELECT * FROM cstam.news WHERE time ORDER BY time DESC LIMIT ?, ?'",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class),
				new Object[] { new Integer(num_A), num_B-num_A });

		if (news.size() != 0)
			return news;
		else
			return null;
	}

	public List<News> getNewsByTime(Date fromtime, Date totime) {
		List<News> news = query("SELECT * FROM cstam.news WHERE time BETWEEN ? AND ? ORDER BY time DESC'",
				ParameterizedBeanPropertyRowMapper.newInstance(News.class),
				new Object[] { fromtime, totime });

		if (news.size() != 0)
			return news;
		else
			return null;
	}
	
	/**************************************
	 * Wrapper for Logger
	 **************************************/
	public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
		try {
			String args_str = "";
			for (Object o: args) {
				if (o == null) {
					args_str += "null ";
				} else {
					args_str += o.toString();
				}
			}
			//LOG.info("query sql " + sql + " (with args: " + args_str + ")");
			return this.jdbcTemplate.query(sql, rowMapper, args);
		} catch (DataAccessException e) {
			e.printStackTrace();
			//LOG.error(e.getMessage());
			throw e;
		}
	}
	
	public int update(String sql, Object ... args) throws DataAccessException {
		try {
			String args_str = "";
			for (Object o: args) {
				if (o == null) {
					args_str += "null ";
				} else {
					args_str += o.toString();
				}
			}
			//LOG.info("update sql " + sql + " (with args: " + args_str + ")");
			return this.jdbcTemplate.update(sql, args);
		} catch (DataAccessException e) {
			e.printStackTrace();
			//LOG.error(e.getMessage());
			throw e;
		}
	}
}
